package com.zhike.b2b.dao;

import java.io.Reader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCallback;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;

import bsh.EvalError;
import bsh.Interpreter;

import com.saas.biz.commen.CommGetId;
import com.saas.biz.commen.FilterChar;
import com.saas.sys.buffer.Buffers;

public class DbExecute {
	
	protected JdbcTemplate jt;
	private ArrayList queryList = new ArrayList();
	private ArrayList paramList = new ArrayList();
	private String strQuery;
	private String strTable;
	private String className;
	Dbtable query = new Dbtable();
	Logger logger = Logger.getLogger(getClass());

	@Resource
	public void setDataSource(DataSource dataSource) {
		jt = new JdbcTemplate(dataSource);
	}

	public String getStrTable() {
		return strTable;
	}

	public void setStrTable(String paramString) {
		strTable = paramString;
	}

	public ArrayList getQueryList() {
		return queryList;
	}

	public String getStrQuery() {
		return strQuery;
	}

	public void setStrQuery(String paramString) {
		strQuery = paramString;
	}

	public void setQueryList(ArrayList paramArrayList) {
		queryList = paramArrayList;
	}

	public void addStrQuery(String paramString) {
		queryList.add(paramString);
	}

	public ArrayList getParamList() {
		return paramList;
	}

	public void setParamList(ArrayList paramArrayList) {
		paramList = paramArrayList;
	}

	public String getClassName() {
		return className;
	}

	public void setClassName(String paramString) {
		className = paramString;
	}

	public void execBizQuery(Buffers paramBuffers) {
		PreparedStatement ps = null;
		if (queryList.isEmpty())
			return;
		ArrayList al = new ArrayList();
		Iterator it = queryList.iterator();
		while (it.hasNext()) {
			ArrayList localArrayList2 = (ArrayList) it.next();
			Iterator localIterator2 = localArrayList2.iterator();
			while (localIterator2.hasNext()) {
				HashMap localHashMap = (HashMap) localIterator2.next();
				if (localHashMap.get("queryString") != null) {
					String str1 = localHashMap.get("queryString").toString();
					strTable = localHashMap.get("queryTable").toString();
					strQuery = localHashMap.get("queryString").toString();
					ArrayList localArrayList3 = (ArrayList) localHashMap
							.get("queryVarMap");
					try {
						strQuery = getQuery(paramBuffers);
						paramList = localArrayList3;
		                logger.debug(strQuery);
						jt.execute(new PreparedStatementCreator() {
							public PreparedStatement createPreparedStatement(Connection conn)
									throws SQLException {
								PreparedStatement ps = conn.prepareStatement(strQuery);
                                setQueryParam(ps, replaceQueryParam());
								return ps;
							}
						},new PreparedStatementCallback<Integer>() {
                              public Integer doInPreparedStatement(
									PreparedStatement arg0)
									throws SQLException, DataAccessException {
								// TODO Auto-generated method stub
								return 0;
							}
						});
						
						al.add(strQuery);
					} catch (Exception localSQLException) {
						
						String str2 = paramBuffers.getString("TRADE_TYPE_CODE");
						if (!str2.equals("0811"))
							throw new RuntimeException(localSQLException);
					} 
				}
			}
		}
		ps = null;
		queryList.clear();
		addLogs(paramBuffers, al);
	}

	public ArrayList FilterChar(ArrayList paramArrayList) {
		ArrayList localArrayList = new ArrayList();
		for (int i = 0; i < paramArrayList.size(); i++)
			localArrayList.add(FilterChar.Repex(paramArrayList.get(i)
					.toString()));
		return localArrayList;
	}
/*
	public void bulidMoreHtml(Buffers paramBuffers) {
		String str1 = paramBuffers.getString("ID");
		String str2 = paramBuffers.getString("TABLENAME");
		String str3 = paramBuffers.getString("OPER_NAME");
		String str4 = "";
		try {
			StringTokenizer st = new StringTokenizer(str1, "|");
			String str5 = "";
			while (st.hasMoreTokens()) {
				str5 = st.nextToken();
				str4 = str4 + new TagControl().buildContent(str2, str5);
				addScore(str5, str2, str3);
			}
		} catch (Exception ex) {
			logger.info(ex.getMessage());
		}
	}*/

/*
	public void addScore(String paramString1, String paramString2,
			String paramString3) {
		logger.debug("执行addScore方法");
		logger.debug("取得当前操作是：" + paramString3);
		String str1 = "";
		if (paramString2.equals("news")) {
			paramString3 = "发布资讯";
			str1 = "select c.cust_id,c.level_id,c.level_name,c.cust_name,cl.news_score score from company c,news n,company_level cl where n.cust_id=c.cust_id and c.level_id=cl.level_id and news_id='"
					+ paramString1 + "'";
		} else if (paramString2.equals("product")) {
			paramString3 = "发布产品";
			str1 = "select c.cust_id,c.level_id,c.level_name,c.cust_name,cl.product_score score from company c,product n,company_level cl where n.cust_id=c.cust_id and c.level_id=cl.level_id and product_id='"
					+ paramString1 + "'";
		} else if (paramString2.equals("buying")) {
			paramString3 = "发布求购";
			str1 = "select c.cust_id,c.level_id,c.level_name,c.cust_name,cl.buying_score score from company c,buying n,company_level cl where n.cust_id=c.cust_id and c.level_id=cl.level_id and buying_id='"
					+ paramString1 + "'";
		} else if (paramString2.equals("sales")) {
			paramString3 = "发布供应";
			str1 = "select c.cust_id,c.level_id,c.level_name,c.cust_name,cl.sales_score score from company c,sales n,company_level cl where n.cust_id=c.cust_id and c.level_id=cl.level_id and sales_id='"
					+ paramString1 + "'";
		} else if (paramString2.equals("out_of_stock")) {
			paramString3 = "缺货登记";
			str1 = "select c.cust_id,c.level_id,c.level_name,c.cust_name,cl.stock_score score from company c,out_of_stock n,company_level cl where n.cust_id=c.cust_id and c.level_id=cl.level_id and trade_id='"
					+ paramString1 + "'";
		}
		ArrayList localArrayList = new ArrayList();
		logger.debug("进入selBizQuerySql方法，查询当前操作信息");
		localArrayList = new Dbexecute().selBizQuerySql(str1);
		String str2 = "";
		String str3 = "";
		String str4 = "";
		String str5 = "";
		String str6 = "";
		String str7 = "";
		if ((localArrayList != null) && (localArrayList.size() > 0)) {
			HashMap localHashMap = (HashMap) localArrayList.get(0);
			str2 = localHashMap.get("cust_id") != null ? localHashMap.get(
					"cust_id").toString() : str2;
			str3 = localHashMap.get("level_id") != null ? localHashMap.get(
					"level_id").toString() : str3;
			str7 = localHashMap.get("score") != null ? localHashMap
					.get("score").toString() : str7;
			str4 = localHashMap.get("level_name") != null ? localHashMap.get(
					"level_name").toString() : str4;
			str5 = localHashMap.get("cust_name") != null ? localHashMap.get(
					"score").toString() : str5;
			str6 = new CompanyInfo().getUser(str2);
			new ScoreListInfo().addScoreListInfo2(str2, str6, paramString3,
					str7, str3, str4, str5, paramString1);
			logger.debug("更新company表的积分数");
			String str8 = "update company set integration = integration + "
					+ str7 + " where cust_id = '" + str2 + "'";
			Connection localConnection = new DbConnect().getConnection();
			try {
				Statement localStatement = localConnection.createStatement();
				localStatement.execute(str8);
				localConnection.commit();
				localStatement = null;
				localConnection.close();
			} catch (SQLException localSQLException) {
				localSQLException.printStackTrace();
			}
		}
	}
*/
	public void addLogs(Buffers paramBuffers, List paramList1) {
		StringBuffer localStringBuffer = new StringBuffer();
		Object localObject1;
		Object localObject2;
		if ((paramList1 != null) && (paramList1.size() > 0)) {
			localObject1 = paramList1.iterator();
			while (((Iterator) localObject1).hasNext()) {
				localObject2 = ((Iterator) localObject1).next();
				localStringBuffer.append(localObject2.toString() + "|");
			}
		}
		try {
			localObject1 = paramBuffers.getString("SESSION_CUST_ID");
			localObject2 = paramBuffers.getStringWeb("SESSION_CUST_NAME");
			String str1 = paramBuffers.getString("SESSION_USER_ID");
			String str2 = paramBuffers.getString("SESSION_USER_NAME");
			String str3 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
					.format(new Date());
			String str4 = localStringBuffer.toString();
			String str5 = paramBuffers.getString("WEBPATH");
			String str6 = new CommGetId()
					.getTradeId("logs_record", "record_id");
			String str7 = "insert into logs_record  (record_id,cust_id,cust_name,user_id,user_name,add_time,menu_path,content) values  ('"
					+ str6
					+ "','"
					+ (String) localObject1
					+ "','"
					+ (String) localObject2
					+ "','"
					+ str1
					+ "','"
					+ str2
					+ "',now(),'"
					+ str5
					+ "','"
					+ str4.replace("'", "''")
					+ "')";
			logger.debug("保存日志的SQL=" + str7);
			jt.execute(str7);
		} catch (Exception localException) {
			logger.debug("保存日志操作出错！");
			localException.printStackTrace();
		}
	}

	public List selBizQuery() {
		
			strQuery = getQuery();
			List data =  jt.query(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn)
						throws SQLException {
					PreparedStatement ps = conn.prepareStatement(strQuery);
					setQueryParam(ps, replaceQueryParam());
					return ps;
				}
			},new ResultSetExtractor<List>() {
                public ArrayList extractData(ResultSet rs) throws SQLException,
						DataAccessException {
                	ArrayList data = new ArrayList();
                	ResultSetMetaData rsmd = rs.getMetaData();
                	int i = rsmd.getColumnCount();
        		
        			while (rs.next()) {
        				HashMap hm = new HashMap();
        				for (int j = 1; j <= i; j++) {
        					String str1 = rsmd.getColumnName(j).toLowerCase();
        					if ((rsmd.getColumnTypeName(j) == "VARCHAR2")
        							|| (rsmd.getColumnTypeName(j) == "CHAR")
        							|| (rsmd.getColumnTypeName(j) == "DATE")) {
        						if (rs.getObject(j) != null)
        							hm.put(str1, rs.getObject(j).toString());
        						else
        							hm.put(str1, rs.getObject(j));
        					} else if (rsmd.getColumnTypeName(j) == "LONG") {
        						if (rs.getCharacterStream(j) != null) {
        							Reader localReader = rs.getCharacterStream(j);
        							String str2 = "";
        							try {
        								str2 = getLargerString(localReader);
        								hm.put(str1, str2);
        							} catch (Exception localException) {
        								throw new RuntimeException(localException);
        							}
        						} else {
        							hm.put(str1, "");
        						}
        					} else
        						hm.put(str1, rs.getObject(j));
        				}
        				data.add(hm);
        			}
        			return data;
				}
			});
			paramList = null;
			return data;
	}

	public void addTable(Dbtable paramDbtable) {
		setQueryList(paramDbtable.getExecuteBy());
	}

	public String convetStrToWeb(String paramString) {
		String str = "";
		if ((paramString == null) || (paramString == ""))
			str = "";
		else
			try {
				str = new String(paramString.getBytes("GB2312"), "ISO8859_1");
			} catch (Exception localException) {
				throw new RuntimeException(localException);
			}
		if ((paramString == null) || (paramString == ""))
			str = "";
		return str;
	}

	public String convetStrToDb(String paramString) {
		String str = "";
		if ((paramString == null) || (paramString == ""))
			str = "";
		else
			try {
				str = new String(paramString.getBytes("ISO8859_1"), "GB2312");
			} catch (Exception localException) {
				throw new RuntimeException(localException);
			}
		if ((paramString == null) || (paramString == ""))
			str = "";
		return str;
	}

	public ArrayList replaceQueryParam() {
		logger.debug("strQuery=" + strQuery.toString());
		logger.debug("paramList=" + paramList);
		if (paramList.isEmpty())
			return null;
		ArrayList localArrayList = new ArrayList();
		int i = 0;
		String[] sqlArr = strQuery.trim().split(" ");
		for (int j = 0; j < sqlArr.length; j++) {
			if (sqlArr[j].length() <= 0)
				continue;
			String[] arrayOfString2 = sqlArr[j].split(",");
			for (int k = 0; k < arrayOfString2.length; k++) {
				if (arrayOfString2[k].length() <= 0)
					continue;
				String[] arrayOfString3 = arrayOfString2[k].split(":");
				for (int m = 0; m < arrayOfString3.length; m++) {
					if ((arrayOfString3[m].length() <= 0)
							|| (!arrayOfString3[m].substring(0, 1).equals("V")))
						continue;
					i += 1;
					Iterator localIterator = paramList.iterator();
					while (localIterator.hasNext()) {
						HashMap localHashMap1 = (HashMap) localIterator.next();
						HashMap localHashMap2 = new HashMap();
						if (localHashMap1.get("paramName") != null) {
							String str = arrayOfString3[m]
									.replaceAll("\\)", "");
							if (localHashMap1.get("paramName").toString()
									.equalsIgnoreCase(":" + str)) {
								int n = strQuery.indexOf(localHashMap1.get(
										"paramName").toString())
										+ localHashMap1.get("paramName")
												.toString().length();
								if (strQuery.length() > n) {
									if ((strQuery.substring(n, n + 1)
											.equalsIgnoreCase(","))
											|| (strQuery.substring(n, n + 1)
													.equalsIgnoreCase(" "))
											|| (strQuery.substring(n, n + 1)
													.equalsIgnoreCase(")"))
											|| (strQuery.substring(n, n + 1)
													.equalsIgnoreCase(""))) {
										strQuery = strQuery.replaceFirst(
												localHashMap1.get("paramName")
														.toString(), "?");
										localHashMap2.put("paramValue",
												localHashMap1.get("paramValue")
														.toString());
										localHashMap2.put("order", new Integer(
												i));
										localArrayList.add(localHashMap2);
									}
								} else {
									strQuery = strQuery.replaceAll(
											localHashMap1.get("paramName")
													.toString(), "?");
									localHashMap2.put("paramValue", FilterChar
											.Repex(localHashMap1.get(
													"paramValue").toString()));
									localHashMap2.put("order", new Integer(i));
									localArrayList.add(localHashMap2);
								}
							}
						}
					}
				}
			}
		}
		return localArrayList;
	}

	public PreparedStatement setQueryParam(
			PreparedStatement ps, ArrayList paramArrayList) {
		if ((paramArrayList == null) || (paramArrayList.isEmpty()))
			return ps;
		Iterator it = paramArrayList.iterator();
		while (it.hasNext()) {
			HashMap localHashMap = (HashMap) it.next();
			int i = new Integer(localHashMap.get("order").toString())
					.intValue();
			try {
				if (localHashMap.get("paramValue").toString().length() > 1000)
					ps.setCharacterStream(i,
							new StringReader(FilterChar.Repex(localHashMap.get(
									"paramValue").toString())), localHashMap
									.get("paramValue").toString().length());
				else
					ps.setObject(i, FilterChar
							.Repex(localHashMap.get("paramValue").toString()));
			} catch (SQLException localSQLException) {
				throw new RuntimeException(localSQLException);
			}
		}
		return ps;
	}

	public String getQuery() {
		String sql = "";
		Interpreter ip = new Interpreter();
		String str2 = strTable;
		String str3 = strQuery;
		try {
			if (getClassName() == null) {
				ip.eval("bar=com.saas.sql.SaasQuery." + str2
						+ "_" + str3);
				sql = (String) ip.get("bar");
				if (sql == null) {
					ip.eval("bar=com.saas.sql.SaasQuery."
							+ str2.toLowerCase() + "_" + str3.toLowerCase());
					sql = (String) ip.get("bar");
				}
				if (sql == null) {
					ip.eval("bar=com.saas.sql.SaasQuery."
							+ str2.toUpperCase() + "_" + str3.toUpperCase());
					sql = (String) ip.get("bar");
				}
				if (sql == null) {
					ip.eval("bar=com.saas.sql.SaasQuery."
							+ str2.toUpperCase() + "_" + str3.toLowerCase());
					sql = (String) ip.get("bar");
				}
			} else {
				ip.eval("bar=com.saas.sql." + getClassName()
						+ "." + str2 + "_" + str3);
				sql = (String) ip.get("bar");
				if (sql == null) {
					ip.eval("bar=com.saas.sql." + getClassName()
							+ "." + str2.toLowerCase() + "_"
							+ str3.toLowerCase());
					sql = (String) ip.get("bar");
				}
				if (sql == null) {
					ip.eval("bar=com.saas.sql." + getClassName()
							+ "." + str2.toUpperCase() + "_"
							+ str3.toUpperCase());
					sql = (String) ip.get("bar");
				}
				if (sql == null) {
					ip.eval("bar=com.saas.sql." + getClassName()
							+ "." + str2.toUpperCase() + "_"
							+ str3.toLowerCase());
					sql = (String) ip.get("bar");
				}
			}
		} catch (EvalError ee) {
			ee.printStackTrace();
		}
		return sql;
	}

	public String getQuery(Buffers paramBuffers) {
		String str1 = "";
		Interpreter inp = new Interpreter();
		String str2 = strTable;
		String str3 = strQuery;
		if (paramBuffers.getClassName() == null)
			paramBuffers.setClassName("SaasQuery");
		try {
			inp.eval("bar=com.saas.sql."
					+ paramBuffers.getClassName() + "." + str2 + "_" + str3);
			str1 = (String) inp.get("bar");
			if (str1 == null) {
				inp.eval("bar=com.saas.sql."
						+ paramBuffers.getClassName() + "."
						+ str2.toLowerCase() + "_" + str3.toLowerCase());
				str1 = (String) inp.get("bar");
			}
			if (str1 == null) {
				inp.eval("bar=com.saas.sql."
						+ paramBuffers.getClassName() + "."
						+ str2.toUpperCase() + "_" + str3.toUpperCase());
				str1 = (String) inp.get("bar");
			}
			if (str1 == null) {
				inp.eval("bar=com.saas.sql."
						+ paramBuffers.getClassName() + "."
						+ str2.toUpperCase() + "_" + str3.toLowerCase());
				str1 = (String) inp.get("bar");
			}
		} catch (EvalError localEvalError) {
			localEvalError.printStackTrace();
		}
		return str1;
	}

	public List selBizQuery(int paramInt1, int paramInt2) {
		  strQuery = getQuery();
          strQuery = strQuery + " limit " + paramInt1 + "," + paramInt2;
          List data = queryBySql(strQuery);
          strQuery = null;
          return data;
	}

	public static String getLargerString(Reader paramReader) throws Exception {
		char[] localObject = new char[1024000];
		char[] arrayOfChar1 = new char[1024];
		int i = 0;
		int j = 0;
		int k = 1024000;
		while (true) {
			i = paramReader.read(arrayOfChar1);
			if (i == -1)
				break;
			if (j + i > k) {
				char[] arrayOfChar2 = new char[k + 1024000];
				System.arraycopy(localObject, 0, arrayOfChar2, 0, j);
				localObject = arrayOfChar2;
				k += 1024000;
			}
			System.arraycopy(arrayOfChar1, 0, localObject, j, i);
			j += i;
		}
		return (String) new String(localObject, 0, j);
	}

	public Object getFromBuffer(String paramString, Buffers paramBuffers) {
		Object localObject = null;
		return localObject;
	}
	public List selBizQuerySql(int paramInt1, int paramInt2,
			String paramString) {

		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSetMetaData rsm = null;
		paramString = paramString + " limit " + paramInt1 + "," + paramInt2;
		List data = queryBySql(paramString);
		queryList.clear();
		return data;
	}
	
	public List selBizQuerySql(String paramString) {
		List data = queryBySql(paramString);
		queryList.clear();
		strQuery = null;
		return data;
	}

	public void update(String sql) {
	    jt.execute(sql);
	}
	
	public List queryBySql(final String sql)
	{
		return jt.query(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql);
				setQueryParam(ps, replaceQueryParam());
				return ps;
			}
		},new ResultSetExtractor<List>() {
            public ArrayList extractData(ResultSet rs) throws SQLException,
					DataAccessException {
            	ArrayList data = new ArrayList();
            	ResultSetMetaData rsmd = rs.getMetaData();
            	int i = rsmd.getColumnCount();
    		
    			while (rs.next()) {
    				HashMap hm = new HashMap();
    				for (int j = 1; j <= i; j++) {
    					String str1 = rsmd.getColumnName(j).toLowerCase();
    					if ((rsmd.getColumnTypeName(j) == "VARCHAR2")
    							|| (rsmd.getColumnTypeName(j) == "CHAR")
    							|| (rsmd.getColumnTypeName(j) == "DATE")) {
    						if (rs.getObject(j) != null)
    							hm.put(str1, rs.getObject(j).toString());
    						else
    							hm.put(str1, rs.getObject(j));
    					} else if (rsmd.getColumnTypeName(j) == "LONG") {
    						if (rs.getCharacterStream(j) != null) {
    							Reader localReader = rs.getCharacterStream(j);
    							String str2 = "";
    							try {
    								str2 = getLargerString(localReader);
    								hm.put(str1, str2);
    							} catch (Exception localException) {
    								throw new RuntimeException(localException);
    							}
    						} else {
    							hm.put(str1, "");
    						}
    					} else
    						hm.put(str1, rs.getObject(j));
    				}
    				data.add(hm);
    			}
    			return data;
			}
		});
	}
}
